package com.ttl.bc.ap.purchaseorder.chemical;

import hibernate.util.HibernateUtil;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.ttl.bc.ap.ap.bean.BCAPBean;
import com.ttl.bc.ap.purchaseorder.bean.BCPurchaseOrderBean;
import com.ttl.bc.ap.purchaseorder.bean.BCPurchaseOrderSubBean;
import com.ttl.bc.ic.item.BCItemBean;
import com.ttl.bc.ic.stklocation.BCStkLocationBean;
import com.ttl.common.db.ADAO;

public class ChemicalDAO extends ADAO {

	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public boolean update(String s, Object o) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();

			BCPurchaseOrderBean po = (BCPurchaseOrderBean)o;
			
            String hql = "FROM BCPurchaseOrderBean where docNo = '"+s+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			
            
            
            for(int i=0; i<resultList.size(); i++){
            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)resultList.get(i);
            	po.setTempMyDesc4(h.getTempMyDesc4());
                session.delete(h);
            }         
			
			String itemCode ="";
			Iterator it = po.getBcPurchaseOrderSubs().iterator();
			while (it.hasNext()) {
				BCPurchaseOrderSubBean sub = (BCPurchaseOrderSubBean) it.next();
				itemCode = sub.getItemCode();
			}
			hql = "From BCItemBean Where code='"+itemCode+"' ";
			q = session.createQuery(hql);
			List<BCItemBean> itemList = q.list();	
			for (BCItemBean item : itemList) {
				po.setTempMyDesc6(""+item.getStockQty());
				po.setTempMyDesc5("MAX:   "+item.getStockMax());
			}
			
			hql = "From BCAPBean Where code='"+po.getApCode()+"' ";
			q = session.createQuery(hql);
			List<BCAPBean> apList = q.list();				
			for (BCAPBean ap : apList) {
				po.setTempMyDesc7(ap.getName1());
			}            
            
			session.save(po);
			session.getTransaction().commit();
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();
			return false;
		}finally {
			session.close();
		}
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public boolean add(Object o) {
		Session session = null;
    
		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			BCPurchaseOrderBean po = (BCPurchaseOrderBean)o;
			
			String itemCode ="";
			Iterator it = po.getBcPurchaseOrderSubs().iterator();
			while (it.hasNext()) {
				BCPurchaseOrderSubBean sub = (BCPurchaseOrderSubBean) it.next();
				itemCode = sub.getItemCode();
			}
			String hql = "From BCItemBean Where code='"+itemCode+"' ";
			Query q = session.createQuery(hql);
			List<BCItemBean> itemList = q.list();	
			for (BCItemBean item : itemList) {
				po.setTempMyDesc6(""+item.getStockQty());
				po.setTempMyDesc5("MAX:   "+item.getStockMax());
			}
			
			hql = "From BCAPBean Where code='"+po.getApCode()+"' ";
			q = session.createQuery(hql);
			List<BCAPBean> apList = q.list();				
			for (BCAPBean ap : apList) {
				po.setTempMyDesc7(ap.getName1());
			}
			
			session.save(po);
			session.getTransaction().commit();

			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}	
	
	
	
	
	@SuppressWarnings("unchecked")
	public List<CalStockItemBean> cal(String whCode, String shelf, String[] itemsArray) {
		
		List<CalStockItemBean> result = null;
		Session session = null;
		
		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			String hql = "SELECT itemCode, sum(qty) FROM BCStkIssueSub2Query " +
					" WHERE isCancel=0 and whCode='"+whCode+"' and shelfCode='"+shelf+"' " +
					" and (itemCode like '2-%' or itemCode like '3-%' ) ";
			
			if(itemsArray!=null) {
				hql+= " and itemCode in (";
				for (int i = 0; i < itemsArray.length; i++) {
					hql += "'"+itemsArray[i]+"',";
				}
				hql+= " '')";
			}
			
			hql	+= " group by itemCode order by itemCode ";
			System.out.println(hql);
			Query q = session.createQuery(hql);
			List<Object> issue2List = q.list();			
			System.out.println(issue2List.size());
			
//			hql = "SELECT itemCode, sum(qty) FROM BCPurchaseOrderSubShowBean " +
//			" WHERE isCancel=0 " +
//			" and (itemCode like '2-%' or itemCode like '3-%' ) ";
//			if(itemsArray!=null) {
//				hql+= " and itemCode in (";
//				for (int i = 0; i < itemsArray.length; i++) {
//					hql += "'"+itemsArray[i]+"',";
//				}
//				hql+= " '')";
//			}
			hql = "SELECT itemCode, sum(qty) FROM VIEW_PO_CHEM_DYESTUFF " +
			" WHERE isCancel=0 and poStatus=0 " +
			" and (itemCode like '2-%' or itemCode like '3-%' ) ";
			if(itemsArray!=null) {
				hql+= " and itemCode in (";
				for (int i = 0; i < itemsArray.length; i++) {
					hql += "'"+itemsArray[i]+"',";
				}
				hql+= " '')";
			}
			
			
			hql	+= " group by itemCode order by itemCode  ";
	
			q = session.createQuery(hql);
			List<Object> poList = q.list();			
			
			
			hql = "FROM BCStkLocationBean WHERE whCode='"+whCode+"' and shelfCode='"+shelf+"' " +
					" and (itemCode like '2-%' or itemCode like '3-%' )";
			if(itemsArray!=null) {
				hql+= " and itemCode in (";
				for (int i = 0; i < itemsArray.length; i++) {
					hql += "'"+itemsArray[i]+"',";
				}
				hql+= " '')  order by itemCode ";
			}
			
						
			q = session.createQuery(hql);
			List<BCStkLocationBean> stkResult = q.list();
			
			hql = "FROM BCItemBean WHERE code in (";
			for(BCStkLocationBean s:stkResult) {
				hql += "'"+s.getItemCode()+"',";
			}
			hql+="'')  order by code ";
			q = session.createQuery(hql);
			List<BCItemBean> itemsResult = q.list();			
			
			ArrayList<CalStockItemBean> items = new ArrayList<CalStockItemBean>();
			
			for (BCItemBean i : itemsResult) {
					
				CalStockItemBean c = new CalStockItemBean();
				
				if (i.getStockMin()==null) i.setStockMin(0f);
				if (i.getStockMax()==null) i.setStockMax(0f);
				if (i.getStockQty()==null) i.setStockQty(0f);
				if (i.getLastBuyPrice()==null) i.setLastBuyPrice(0d);
				
				Iterator<Object> is2t = issue2List.iterator();
				while (is2t.hasNext()) {
					Object[] tuple = (Object[]) is2t.next();
					
					if(tuple[0].toString().equalsIgnoreCase(i.getCode()) &&
							i.getStockQty().doubleValue()-Double.valueOf(tuple[1].toString()).doubleValue() < i.getStockMin().doubleValue() ) {
			
						c.setItemCode(i.getCode());
						c.setItemName(i.getName1());
						c.setStockQty(i.getStockQty());
						c.setStockMin(i.getStockMin());
						c.setStockMax(i.getStockMax());
						c.setQtyReserved(Float.valueOf(tuple[1].toString()));
						c.setLastBuyPrice(i.getLastBuyPrice().floatValue());
						items.add(c);
					}					
				}				
				
				Iterator<Object> itpo = poList.iterator();
				while (itpo.hasNext()) {
					Object[] tuple = (Object[]) itpo.next();
					
					if(tuple[0].toString().equalsIgnoreCase(i.getCode())  ) {
						if(c.getItemCode()==null) {
							c.setItemCode(i.getCode());
							c.setItemName(i.getName1());
							c.setStockQty(i.getStockQty());
							c.setStockMin(i.getStockMin());
							c.setStockMax(i.getStockMax());
							c.setQtyReserved(0f);
							c.setLastBuyPrice(i.getLastBuyPrice().floatValue());
							items.add(c);	
						}
						c.setQtyOfPO(Float.valueOf(tuple[1].toString()));											
					}					
				}				
				
				if(i.getStockMin().doubleValue()>=i.getStockQty().doubleValue()) {
					if(c.getItemCode()==null) {
						c.setItemCode(i.getCode());
						c.setItemName(i.getName1());					
						c.setStockQty(i.getStockQty());
						c.setStockMin(i.getStockMin());
						c.setStockMax(i.getStockMax());
						c.setQtyReserved(0f);
						c.setLastBuyPrice(i.getLastBuyPrice().floatValue());
						items.add(c);						
					}
				}
			}
			
			Calendar current  = Calendar.getInstance();
//			String start = ""+(current.get(Calendar.YEAR)-1)+"-"+(current.get(Calendar.MONTH)+1)+"-1";
			String start = ChemicalBO.getBeginLastMonth(12);
//			String end   = ""+ current.get(Calendar.YEAR)+"-"+(current.get(Calendar.MONTH)+1)+"-1";
			String end = ChemicalBO.getEndLastMonth();
			
/*			DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
*//*			Date dateFrom = format.parse(start);
			Date dateTo = format.parse(end);*/
			
			hql = "SELECT itemCode, sum(qty)/12 FROM StkIssueSubBean WHERE " +
					" isCancel=0 "+
					" and docDate between '"+start+"' and '"+end+"' " +
					" and whCode='"+whCode+"' and shelfCode in ('12','16','17') "+
					" and itemCode in (";
					for(BCStkLocationBean s:stkResult) {
						hql += "'"+s.getItemCode()+"',";
					}
					hql+="'') ";
					hql+=" group by itemCode order by itemCode ";
			System.out.println(hql);
			q = session.createQuery(hql);
			
			List<Object> itemSumResult = q.list();

			Iterator<Object> it = itemSumResult.iterator();
			while (it.hasNext()) {
				Object[] tuple = (Object[]) it.next();
			
				for (int i = 0; i < items.size(); i++) {
					CalStockItemBean b = (CalStockItemBean)items.get(i);
					if(tuple[0].toString().equalsIgnoreCase(b.getItemCode())) {
						
						float a = Float.valueOf(tuple[1].toString());
						b.setQtyOf12month(a);
						
//						  BigDecimal bigDecimal_Apple_price = 
//							  new BigDecimal("52.2516");
//							  System.out.print("java.math package BigDecimal class" +
//							  " methods example: \n"
//							  + (bigDecimal_Apple_price
//							  .setScale(2,BigDecimal.ROUND_HALF_UP))
//							  .floatValue() + "\n");
//						
//						BigDecimal big = new BigDecimal(Double.valueOf(""+tuple[1])/12);
//						float n =big.setScale(2, BigDecimal.ROUND_HALF_UP).floatValue();
					}
				}
			}

			start = ChemicalBO.getBeginLastMonth(3);
			end = ""+ current.get(Calendar.YEAR)+"-"+(current.get(Calendar.MONTH)+1)+"-"+current.get(Calendar.DAY_OF_MONTH);
			
			hql = "SELECT itemCode, sum(qty)/3 FROM StkIssueSubBean WHERE " +
					" isCancel=0 "+
					" and docDate between '"+start+"' and '"+end+"' " +
					" and whCode='"+whCode+"' and shelfCode in ('12','16','17') "+
					" and itemCode in (";
					for(BCStkLocationBean s:stkResult) {
						hql += "'"+s.getItemCode()+"',";
					}
					hql+="'') ";
					hql+=" group by itemCode order by itemCode ";
//			System.out.println(hql);
					
			q = session.createQuery(hql);
						
			List<Object> itemSum3Result = q.list();

			it = itemSum3Result.iterator();
			while (it.hasNext()) {
				Object[] tuple = (Object[]) it.next();
			
				for (int i = 0; i < items.size(); i++) {
					CalStockItemBean b = (CalStockItemBean)items.get(i);
					if(tuple[0].toString().equalsIgnoreCase(b.getItemCode())) {
						
						float a = Float.valueOf(tuple[1].toString());
						b.setQtyOf3month(a);
						
					}
				}
			}
			
			for (CalStockItemBean c : items) {
				if(c.getQtyOf12month()==null) 
					c.setQtyOf12month(0f);
				if(c.getQtyOf3month()==null) 
					c.setQtyOf3month(0f);
				
				if(c.getQtyOf12month().floatValue()*0.3<=c.getQtyOf3month().floatValue() || c.getQtyOf3month().floatValue()==0) {
					  c.setQtyForOrder(Math.round(c.getQtyOf12month()*0.3f)*1f);
				}else {
					  c.setQtyForOrder(Math.round(c.getQtyOf3month())*1f);							  
				}				
			}
			
			result  =items;
			session.getTransaction().commit();						
			
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		return result;
		
	}

	public List<CalStockItemBean> createpo(String shelf, String[] itemCode) {

		return cal("10", shelf, itemCode);
	}

	@SuppressWarnings("unchecked")
	public List<ChemicalHistoryBean> chemicalHistory(String whCode, String shelf, String[] itemCode) {

		List<ChemicalHistoryBean> result = null;
		Session session = null;
		
		try {
			SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
						
			List<ChemicalHistoryBean> calendarlist = new ChemicalBO().get12months(itemCode[0]);
			
//			Calendar current  = Calendar.getInstance();
			//System.out.println(Calendar.YEAR);
//			String start = ""+(current.get(Calendar.YEAR)-1)+"-"+current.get(Calendar.MONTH)+"-1";
//			String end = ""+(current.get(Calendar.YEAR))+"-"+current.get(Calendar.MONTH)+"-1";
			//System.out.println(start);
//			DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//			Date dateFrom = format.parse(start);
//			Date dateTo = format.parse(end);
			
			String hql = "SELECT itemCode, year_, month_, sum(qty) FROM ViewBCStkIssueSub WHERE " +
					" isCancel=0 "+
//					" and docDate between :start and :end " +
					" and whCode='"+whCode+"' and shelfCode in ('12','16','17') ";
//					" and itemCode in (";
					if(itemCode!=null) {
						hql+= " and itemCode in (";
						for (int i = 0; i < itemCode.length; i++) {
							hql += "'"+itemCode[i]+"',";
						}
						hql+= " '')";
					}
					
					hql+=" group by itemCode, year_, month_" +
						 " order by itemCode, year_, month_";
					System.out.println(hql);
			Query q = session.createQuery(hql);
			
//			q.setParameter("start", dateFrom);
//			q.setParameter("end", dateTo);
//			q.setParameter("shelf", shelf);
			
			List<Object> itemSumResult = q.list();
//			System.out.println("itemSumResult="+itemSumResult.size());
			
			Iterator<Object> it = itemSumResult.iterator();
			while (it.hasNext()) {
				Object[] tuple = (Object[]) it.next();
				
				for (int i = 0; i < calendarlist.size(); i++) {
					ChemicalHistoryBean c = (ChemicalHistoryBean)calendarlist.get(i);
					if(tuple[0].toString().equalsIgnoreCase(c.getItemCode()) &&
						Integer.valueOf(tuple[1].toString()).intValue() == c.getYear_().intValue() &&
						Integer.valueOf(tuple[2].toString()).intValue() == c.getMonth_().intValue() ) {

						c.setUsedQty(c.getUsedQty().floatValue()+Float.valueOf(tuple[3].toString()) );							  
					}
				}
			}			
			
			result  =calendarlist;
			session.getTransaction().commit();						
			
		}catch(Exception e) {			
			System.out.println(e.toString());
		}finally {
			session.close();
		}
		return result;
	}

	@SuppressWarnings("rawtypes")
	public boolean cancel(String docNo) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCPurchaseOrderBean where docNo = '"+docNo+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			
            
            for(int i=0; i<resultList.size(); i++){
            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)resultList.get(i);
            	h.setIsCancel(1);
            	Iterator it = h.getBcPurchaseOrderSubs().iterator();
            	while (it.hasNext()) {
            		BCPurchaseOrderSubBean sub = (BCPurchaseOrderSubBean) it.next();
					sub.setIsCancel(1);
				}
                session.save(h);
            }            
            
			session.getTransaction().commit();
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();
			return false;
		}finally {
			session.close();
		}	
	}
}
